package com.cyjf.dbview.mapper;

import com.cyjf.dbview.bo.ChannelBO;
import com.cyjf.dbview.bo.OrderBO;
import com.cyjf.dbview.bo.StaffBO;
import com.cyjf.dbview.model.ZscarDealer;
import org.apache.ibatis.annotations.Select;
import tk.mybatis.mapper.common.Mapper;

import java.util.List;

public interface ZscarDealerMapper extends Mapper<ZscarDealer> {

    @Select("select count(DISTINCT dealer_id) as todayaddAmount from zscartm1jucom2.zscar_dealer " +
            "WHERE  FROM_UNIXTIME(dealer_addtime,'%Y/%m/%d')=date_sub(date(now()),interval 1 day) " +
            "and dealer_name!=' ' and dealer_stop=1")
    Integer getToadyAddAmount();

    @Select("select count(DISTINCT dealer_id) as \"totalAmount\"\n" +
            "from zscartm1jucom2.zscar_dealer\n" +
            "WHERE  FROM_UNIXTIME(dealer_addtime,'%Y/%m/%d')<DATE(NOW()) \n" +
            "and dealer_name!=' ' and dealer_stop=1")
    Integer getTotalAmount();

    @Select("SELECT\n" +
            "\tg.staffName AS 'staffName',\n" +
            "\tCOUNT(DISTINCT aa.dealer_id) AS 'staffDealerNumber'\n" +
            "FROM\n" +
            "\tzscartm1jucom2.zscar_dealer aa\n" +
            "LEFT JOIN zscartm1jucom2.crm_staff_dealer f ON aa.dealer_id = f.dealerId\n" +
            "LEFT JOIN zscartm1jucom2.crm_sales_staff g ON f.staffId = g.staffid\n" +
            "WHERE\n" +
            "\tf.`status` = 1\n" +
            "GROUP BY\n" +
            "\tg.staffName\n" +
            "ORDER BY COUNT(DISTINCT aa.dealer_id) DESC")
    List<StaffBO> getStaffDealer();


    @Select("SELECT\n" +
            "\tb.provinces_name AS \"provinces\",\n" +
            "\tCOUNT(DISTINCT a.dealer_id) AS \"dealerNumber\"\n" +
            "FROM\n" +
            "\tzscartm1jucom2.zscar_dealer a\n" +
            "LEFT JOIN zscartm1jucom2.zscar_provinces b ON a.dealer_provinces = b.provinces_id\n" +
            "GROUP BY\n" +
            "\tb.provinces_name")
    List<StaffBO> getDealerAreaAmount();


    @Select("SELECT\n" +
            "from_unixtime(create_time,'%Y/%m/%d') as orderDate,\n" +
            "ROUND(SUM(amount)/10000,2) as orderAmount\n" +
            "FROM  zscartm1jucom2.zscar_sales_order_paymentdocument\n" +
            "WHERE deal_status=2 and pay_status=1 and from_unixtime(create_time,'%Y/%m/%d')  BETWEEN '2019/01/14' and '2019/01/21'\n" +
            "GROUP BY from_unixtime(create_time,'%Y/%m/%d') ")
    List<OrderBO> orderCount();

    //渠道占比详情
    @Select("SELECT\n" +
            " c.channel_name as name,\n" +
            " COUNT(DISTINCT a.dealer_id) AS 'dealerNum',\n" +
            " COUNT(DISTINCT a.dealer_id) / (\n" +
            "  SELECT\n" +
            "   COUNT(DISTINCT dealer_id)\n" +
            "  FROM\n" +
            "   zscartm1jucom2.zscar_dealer\n" +
            "  WHERE\n" +
            "   dealer_stop = 1 and dealer_name!=''\n" +
            " ) AS \"\n" +
            "Proportion\"\n" +
            "FROM\n" +
            " zscartm1jucom2.zscar_dealer a\n" +
            "LEFT JOIN zscartm1jucom2.dealer_channel_relation b ON a.dealer_id = b.dealer_id\n" +
            "LEFT JOIN zscartm1jucom2.dealer_channel_type c ON b.channel_id = c.channel_id\n" +
            "WHERE\n" +
            " a.dealer_stop = 1  and a.dealer_name!=''\n" +
            "GROUP BY\n" +
            " c.channel_name")
    List<ChannelBO> channelProportion();
}